

<!DOCTYPE html>
<html lang="zh-CN" data-default-color-scheme=auto>



<head>
  <meta charset="UTF-8">
  <link rel="apple-touch-icon" sizes="76x76" href="/img/favicon.png">
  <link rel="icon" href="/img/favicon.png">
  <meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=5.0, shrink-to-fit=no">
  <meta http-equiv="x-ua-compatible" content="ie=edge">
  
  <meta name="theme-color" content="#2f4154">
  <meta name="author" content="John Doe">
  <meta name="keywords" content="">
  
    <meta name="description" content="MySQL基础 今日目标：   完成MySQL的安装及登陆基本操作 能通过SQL对数据库进行CRUD 能通过SQL对表进行CRUD 能通过SQL对数据进行CRUD   1，数据库相关概念 以前我们做系统，数据持久化的存储采用的是文件存储。存储到文件中可以达到系统关闭数据不会丢失的效果，当然文件存储也有它的弊端。 假设在文件中存储以下的数据： 1234姓名	年龄	性别	住址">
<meta property="og:type" content="article">
<meta property="og:title" content="mysql基础">
<meta property="og:url" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/index.html">
<meta property="og:site_name" content="Hexo">
<meta property="og:description" content="MySQL基础 今日目标：   完成MySQL的安装及登陆基本操作 能通过SQL对数据库进行CRUD 能通过SQL对表进行CRUD 能通过SQL对数据进行CRUD   1，数据库相关概念 以前我们做系统，数据持久化的存储采用的是文件存储。存储到文件中可以达到系统关闭数据不会丢失的效果，当然文件存储也有它的弊端。 假设在文件中存储以下的数据： 1234姓名	年龄	性别	住址">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505162830398.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505162853071.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505162907707.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505162926678.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505162941586.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505162954700.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163035771.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163107283.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163125325.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163206913.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163409998.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163627662.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163635980.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163649872.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163701312.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163710605.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163718592.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163727384.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163737082.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163744311.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163754124.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163813305.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163825351.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163835382.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163854797.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163903161.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163911161.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163920284.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163931228.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163941571.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163948376.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163956411.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164004845.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164038977.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164101427.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164122654.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164132470.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164140046.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164151158.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164208464.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164401008.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164412987.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164429919.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164436618.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164449264.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164501098.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164507470.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164518873.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164620069.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164720665.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164728112.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164737419.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164759177.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164811149.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164820312.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164846978.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164853885.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/assets/image-20210722190508272.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164912745.png">
<meta property="og:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164931399.png">
<meta property="article:published_time" content="2022-05-04T16:00:00.000Z">
<meta property="article:modified_time" content="2022-05-05T08:49:41.412Z">
<meta property="article:author" content="John Doe">
<meta property="article:tag" content="mysql基础">
<meta name="twitter:card" content="summary_large_image">
<meta name="twitter:image" content="http://example.com/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505162830398.png">
  
  
  <title>mysql基础 - Hexo</title>

  <link  rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4/dist/css/bootstrap.min.css" />


  <link  rel="stylesheet" href="https://cdn.jsdelivr.net/npm/github-markdown-css@4/github-markdown.min.css" />
  <link  rel="stylesheet" href="https://cdn.jsdelivr.net/npm/hint.css@2/hint.min.css" />

  
    
    
      
      <link  rel="stylesheet" href="https://cdn.jsdelivr.net/npm/highlight.js@10/styles/github-gist.min.css" />
    
  

  
    <link  rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@3/dist/jquery.fancybox.min.css" />
  


<!-- 主题依赖的图标库，不要自行修改 -->

<link rel="stylesheet" href="//at.alicdn.com/t/font_1749284_ba1fz6golrf.css">



<link rel="stylesheet" href="//at.alicdn.com/t/font_1736178_lbnruvf0jn.css">


<link  rel="stylesheet" href="/css/main.css" />

<!-- 自定义样式保持在最底部 -->


  <script id="fluid-configs">
    var Fluid = window.Fluid || {};
    var CONFIG = {"hostname":"example.com","root":"/","version":"1.8.14","typing":{"enable":true,"typeSpeed":70,"cursorChar":"_","loop":false},"anchorjs":{"enable":true,"element":"h1,h2,h3,h4,h5,h6","placement":"right","visible":"hover","icon":""},"progressbar":{"enable":true,"height_px":3,"color":"#29d","options":{"showSpinner":false,"trickleSpeed":100}},"copy_btn":true,"image_zoom":{"enable":true,"img_url_replace":["",""]},"toc":{"enable":true,"headingSelector":"h1,h2,h3,h4,h5,h6","collapseDepth":0},"lazyload":{"enable":true,"loading_img":"/img/loading.gif","onlypost":false,"offset_factor":2},"web_analytics":{"enable":false,"baidu":null,"google":null,"gtag":null,"tencent":{"sid":null,"cid":null},"woyaola":null,"cnzz":null,"leancloud":{"app_id":null,"app_key":null,"server_url":null,"path":"window.location.pathname","ignore_local":false}},"search_path":"/local-search.xml"};
  </script>
  <script  src="/js/utils.js" ></script>
  <script  src="/js/color-schema.js" ></script>
<meta name="generator" content="Hexo 6.1.0"></head>


<body>
  <header style="height: 70vh;">
    <nav id="navbar" class="navbar fixed-top  navbar-expand-lg navbar-dark scrolling-navbar">
  <div class="container">
    <a class="navbar-brand" href="/">
      <strong>s.un的藏书阁</strong>
    </a>

    <button id="navbar-toggler-btn" class="navbar-toggler" type="button" data-toggle="collapse"
            data-target="#navbarSupportedContent"
            aria-controls="navbarSupportedContent" aria-expanded="false" aria-label="Toggle navigation">
      <div class="animated-icon"><span></span><span></span><span></span></div>
    </button>

    <!-- Collapsible content -->
    <div class="collapse navbar-collapse" id="navbarSupportedContent">
      <ul class="navbar-nav ml-auto text-center">
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/">
                <i class="iconfont icon-home-fill"></i>
                首页
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/archives/">
                <i class="iconfont icon-archive-fill"></i>
                归档
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/categories/">
                <i class="iconfont icon-category-fill"></i>
                分类
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/tags/">
                <i class="iconfont icon-tags-fill"></i>
                标签
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/about/">
                <i class="iconfont icon-user-fill"></i>
                关于
              </a>
            </li>
          
        
        
          <li class="nav-item" id="search-btn">
            <a class="nav-link" target="_self" href="javascript:;" data-toggle="modal" data-target="#modalSearch" aria-label="Search">
              &nbsp;<i class="iconfont icon-search"></i>&nbsp;
            </a>
          </li>
        
        
          <li class="nav-item" id="color-toggle-btn">
            <a class="nav-link" target="_self" href="javascript:;" aria-label="Color Toggle">&nbsp;<i
                class="iconfont icon-dark" id="color-toggle-icon"></i>&nbsp;</a>
          </li>
        
      </ul>
    </div>
  </div>
</nav>

    <div class="banner" id="banner" parallax=true
         style="background: url('/img/ying.jpg') no-repeat center center;
           background-size: cover;">
      <div class="full-bg-img">
        <div class="mask flex-center" style="background-color: rgba(0, 0, 0, 0.3)">
          <div class="page-header text-center fade-in-up">
            <span class="h2" id="subtitle" title="mysql基础">
              
            </span>

            
              <div class="mt-3">
  
  
    <span class="post-meta">
      <i class="iconfont icon-date-fill" aria-hidden="true"></i>
      <time datetime="2022-05-05 00:00" pubdate>
        2022年5月5日 凌晨
      </time>
    </span>
  
</div>

<div class="mt-1">
  
    <span class="post-meta mr-2">
      <i class="iconfont icon-chart"></i>
      14k 字
    </span>
  

  
    <span class="post-meta mr-2">
      <i class="iconfont icon-clock-fill"></i>
      
      
      115 分钟
    </span>
  

  
  
</div>

            
          </div>

          
        </div>
      </div>
    </div>
  </header>

  <main>
    
      

<div class="container-fluid nopadding-x">
  <div class="row nomargin-x">
    <div class="d-none d-lg-block col-lg-2"></div>
    <div class="col-lg-8 nopadding-x-md">
      <div class="container nopadding-x-md" id="board-ctn">
        <div class="py-5" id="board">
          <article class="post-content mx-auto">
            <!-- SEO header -->
            <h1 style="display: none">mysql基础</h1>
            
            <div class="markdown-body">
              <h2 id="mysql基础">MySQL基础</h2>
<p><strong>今日目标：</strong></p>
<blockquote>
<ul>
<li>完成MySQL的安装及登陆基本操作</li>
<li>能通过SQL对数据库进行CRUD</li>
<li>能通过SQL对表进行CRUD</li>
<li>能通过SQL对数据进行CRUD</li>
</ul>
</blockquote>
<h2 id="数据库相关概念">1，数据库相关概念</h2>
<p>以前我们做系统，数据持久化的存储采用的是文件存储。存储到文件中可以达到系统关闭数据不会丢失的效果，当然文件存储也有它的弊端。</p>
<p>假设在文件中存储以下的数据：</p>
<figure class="highlight"><table><tr><td class="gutter"><div class="code-wrapper"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></div></td><td class="code"><pre><code class="hljs">姓名	年龄	性别	住址<br>张三	23	男	北京西三旗<br>李四	24	女	北京西二旗<br>王五	25	男	西安软件新城<br></code></pre></td></tr></table></figure>
<p>现要修改李四这条数据的性别数据改为男，我们现学习的IO技术可以通过将所有的数据读取到内存中，然后进行修改再存到该文件中。通过这种方式操作存在很大问题，现在只有三条数据，如果文件中存储1T的数据，那么就会发现内存根本就存储不了。</p>
<p>现需要既能持久化存储数据，也要能避免上述问题的技术使用在我们的系统中。数据库就是这样的一门技术。</p>
<h3 id="数据库">1.1 数据库</h3>
<ul>
<li><p>==存储和管理数据的仓库，数据是有组织的进行存储。==</p></li>
<li><p>数据库英文名是 DataBase，简称DB。</p></li>
</ul>
<p>数据库就是将数据存储在硬盘上，可以达到持久化存储的效果。那又是如何解决上述问题的？使用数据库管理系统。</p>
<h3 id="数据库管理系统">1.2 数据库管理系统</h3>
<ul>
<li>==管理数据库的大型软件==</li>
<li>英文：DataBase Management System，简称 DBMS</li>
</ul>
<p>在电脑上安装了数据库管理系统后，就可以通过数据库管理系统创建数据库来存储数据，也可以通过该系统对数据库中的数据进行数据的增删改查相关的操作。我们平时说的MySQL数据库其实是MySQL数据库管理系统。</p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505162830398.png" srcset="/img/loading.gif" lazyload class="">
<p>通过上面的描述，大家应该已经知道了 <code>数据库管理系统</code> 和
<code>数据库</code> 的关系。那么有有哪些常见的数据库管理系统呢？</p>
<h3 id="常见的数据库管理系统">1.3 常见的数据库管理系统</h3>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505162853071.png" srcset="/img/loading.gif" lazyload class="">
<p>接下来对上面列举的数据库管理系统进行简单的介绍：</p>
<ul>
<li>Oracle：收费的大型数据库，Oracle 公司的产品</li>
<li>==MySQL==： 开源免费的中小型数据库。后来 Sun公司收购了 MySQL，而 Sun
公司又被 Oracle 收购</li>
<li>SQL Server：MicroSoft 公司收费的中型的数据库。C#、.net
等语言常使用</li>
<li>PostgreSQL：开源免费中小型的数据库</li>
<li>DB2：IBM 公司的大型收费数据库产品</li>
<li>SQLite：嵌入式的微型数据库。如：作为 Android 内置数据库</li>
<li>MariaDB：开源免费中小型的数据库</li>
</ul>
<p>我们课程上学习的是MySQL数据库管理系统，PostgreSQL在一些公司也有使用，此时大家肯定会想以后在公司中如果使用我们没有学习过程的PostgreSQL数据库管理系统怎么办？这点大家大可不必担心，如下图所示：</p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505162907707.png" srcset="/img/loading.gif" lazyload class="">
<p>我们可以通过数据库管理系统操作数据库，对数据库中的数据进行增删改查操作，而怎么样让用户跟数据库管理系统打交道呢？就可以通过一门编程语言（SQL）来实现。</p>
<h3 id="sql">1.4 SQL</h3>
<ul>
<li>英文：Structured Query Language，简称 SQL，结构化查询语言</li>
<li>操作关系型数据库的编程语言</li>
<li>定义操作所有关系型数据库的统一标准，可以使用SQL操作所有的关系型数据库管理系统，以后工作中如果使用到了其他的数据库管理系统，也同样的使用SQL来操作。</li>
</ul>
<h2 id="mysql">2，MySQL</h2>
<h3 id="mysql安装">2.1 MySQL安装</h3>
<blockquote>
<p><strong>安装环境:Win10 64位</strong> <strong>软件版本:MySQL 5.7.24
解压版</strong></p>
</blockquote>
<h4 id="下载">2.1.1 下载</h4>
<p>https://downloads.mysql.com/archives/community/</p>
<p>点开上面的链接就能看到如下界面：</p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505162926678.png" srcset="/img/loading.gif" lazyload class="">
<p>选择选择和自己<strong>系统位数</strong>相对应的版本点击右边的<code>Download</code>，此时会进到另一个页面，同样在接近页面底部的地方找到如下图所示的位置：</p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505162941586.png" srcset="/img/loading.gif" lazyload class="">
<p>不用理会上面的登录和注册按钮，直接点击
<code>No thanks, just start my download.</code> 就可以下载。</p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505162954700.png" srcset="/img/loading.gif" lazyload class="">
<h4 id="安装解压">2.1.2 安装(解压)</h4>
<p>下载完成后我们得到的是一个压缩包，将其解压，我们就可以得到MySQL
5.7.24的软件本体了(就是一个文件夹)，我们可以把它放在你想安装的位置。</p>
<hr />
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163035771.png" srcset="/img/loading.gif" lazyload class="">
<h3 id="mysql卸载">2.2 MySQL卸载</h3>
<p>如果你想卸载MySQL，也很简单。</p>
<p>右键开始菜单，选择<code>命令提示符(管理员)</code>，打开黑框。</p>
<ol type="1">
<li>敲入<code>net stop mysql</code>，回车。</li>
</ol>
<figure class="highlight arduino"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs arduino">net stop mysql<br></code></pre></td></tr></table></figure>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163107283.png" srcset="/img/loading.gif" lazyload class="">
<ol start="2" type="1">
<li>再敲入<code>mysqld -remove mysql</code>，回车。</li>
</ol>
<figure class="highlight routeros"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs routeros">mysqld -<span class="hljs-built_in">remove</span> mysql<br></code></pre></td></tr></table></figure>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163125325.png" srcset="/img/loading.gif" lazyload class="">
<ol start="3" type="1">
<li>最后删除MySQL目录及相关的环境变量。</li>
</ol>
<p><strong>至此，MySQL卸载完成！</strong></p>
<h3 id="mysql配置">2.3 MySQL配置</h3>
<h4 id="添加环境变量">2.3.1 添加环境变量</h4>
<blockquote>
<p>环境变量里面有很多选项，这里我们只用到<code>Path</code>这个参数。为什么在初始化的开始要添加环境变量呢？
在黑框(即CMD)中输入一个可执行程序的名字，Windows会先在环境变量中的<code>Path</code>所指的路径中寻找一遍，如果找到了就直接执行，没找到就在当前工作目录找，如果还没找到，就报错。我们添加环境变量的目的就是能够在任意一个黑框直接调用MySQL中的相关程序而不用总是修改工作目录，大大简化了操作。</p>
</blockquote>
<p>右键<code>此电脑</code>→<code>属性</code>，点击<code>高级系统设置</code></p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163206913.png" srcset="/img/loading.gif" lazyload class="">
<p>点击<code>环境变量</code></p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163409998.png" srcset="/img/loading.gif" lazyload class="">
<p>在<code>系统变量</code>中新建MYSQL_HOME</p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163627662.png" srcset="/img/loading.gif" lazyload class="">
<p>在<code>系统变量</code>中找到并<strong>双击</strong><code>Path</code></p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163635980.png" srcset="/img/loading.gif" lazyload class="">
<p>点击<code>新建</code></p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163649872.png" srcset="/img/loading.gif" lazyload class="">
<p>最后点击确定。</p>
<p><strong>如何验证是否添加成功？</strong></p>
<p>右键开始菜单(就是屏幕左下角)，选择<code>命令提示符(管理员)</code>，打开黑框，敲入<code>mysql</code>，回车。
如果提示<code>Can't connect to MySQL server on 'localhost'</code>则证明添加成功；
如果提示<code>mysql不是内部或外部命令，也不是可运行的程序或批处理文件</code>则表示添加添加失败，请重新检查步骤并重试。</p>
<h4 id="新建配置文件">2.3.2 新建配置文件</h4>
<p>新建一个文本文件，内容如下：</p>
<figure class="highlight properties"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><code class="hljs properties"><span class="hljs-attr">[mysql]</span><br><span class="hljs-attr">default-character-set</span>=<span class="hljs-string">utf8</span><br><br><span class="hljs-attr">[mysqld]</span><br><span class="hljs-attr">character-set-server</span>=<span class="hljs-string">utf8</span><br><span class="hljs-attr">default-storage-engine</span>=<span class="hljs-string">INNODB</span><br><span class="hljs-attr">sql_mode</span>=<span class="hljs-string">STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION</span><br></code></pre></td></tr></table></figure>
<p>把上面的文本文件另存为，在保存类型里选<code>所有文件 (*.*)</code>，文件名叫<code>my.ini</code>，存放的路径为MySQL的<code>根目录</code>(例如我的是<code>D:\software\mysql-5.7.24-winx64</code>,根据自己的MySQL目录位置修改)。</p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163701312.png" srcset="/img/loading.gif" lazyload class="">
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163710605.png" srcset="/img/loading.gif" lazyload class="">
<p>上面代码意思就是配置数据库的默认编码集为utf-8和默认存储引擎为INNODB。</p>
<h4 id="初始化mysql">2.3.3 初始化MySQL</h4>
<p>在刚才的黑框中敲入<code>mysqld --initialize-insecure</code>，回车，稍微等待一会，如果出现没有出现报错信息(如下图)则证明data目录初始化没有问题，此时再查看MySQL目录下已经有data目录生成。</p>
<figure class="highlight ada"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs ada">mysqld <span class="hljs-comment">--initialize-insecure</span><br></code></pre></td></tr></table></figure>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163718592.png" srcset="/img/loading.gif" lazyload class="">
<p>tips：如果出现如下错误</p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163727384.png" srcset="/img/loading.gif" lazyload class="">
<p>是由于权限不足导致的，去<code>C:\Windows\System32</code>
下以管理员方式运行 cmd.exe</p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163737082.png" srcset="/img/loading.gif" lazyload class="">
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163744311.png" srcset="/img/loading.gif" lazyload class="">
<h4 id="注册mysql服务">2.3.4 注册MySQL服务</h4>
<p>在黑框里敲入<code>mysqld -install</code>，回车。</p>
<figure class="highlight cmake"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs cmake">mysqld -<span class="hljs-keyword">install</span><br></code></pre></td></tr></table></figure>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163754124.png" srcset="/img/loading.gif" lazyload class="">
<p>现在你的计算机上已经安装好了MySQL服务了。</p>
<p>MySQL服务器</p>
<h4 id="启动mysql服务">2.3.5 启动MySQL服务</h4>
<p>在黑框里敲入<code>net start mysql</code>，回车。</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><code class="hljs java">net start mysql  <span class="hljs-comment">// 启动mysql服务</span><br>    <br>net stop mysql  <span class="hljs-comment">// 停止mysql服务</span><br></code></pre></td></tr></table></figure>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163813305.png" srcset="/img/loading.gif" lazyload class="">
<h4 id="修改默认账户密码">2.3.6 修改默认账户密码</h4>
<p>在黑框里敲入<code>mysqladmin -u root password 1234</code>，这里的<code>1234</code>就是指默认管理员(即root账户)的密码，可以自行修改成你喜欢的。</p>
<figure class="highlight apache"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs apache"><span class="hljs-attribute">mysqladmin</span> -u root password <span class="hljs-number">1234</span><br></code></pre></td></tr></table></figure>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163825351.png" srcset="/img/loading.gif" lazyload class="">
<p><strong>至此，MySQL 5.7 解压版安装完毕！</strong></p>
<h3 id="mysql登陆和退出">2.4 MySQL登陆和退出</h3>
<h4 id="登陆">2.4.1 登陆</h4>
<p>右键开始菜单，选择<code>命令提示符</code>，打开黑框。
在黑框中输入，<code>mysql -uroot -p1234</code>，回车，出现下图且左下角为<code>mysql&gt;</code>，则登录成功。</p>
<figure class="highlight nginx"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs nginx"><span class="hljs-attribute">mysql</span> -uroot -p1234<br></code></pre></td></tr></table></figure>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163835382.png" srcset="/img/loading.gif" lazyload class="">
<p><strong>到这里你就可以开始你的MySQL之旅了！</strong></p>
<p>登陆参数：</p>
<figure class="highlight apache"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs apache"><span class="hljs-attribute">mysql</span> -u用户名 -p密码 -h要连接的mysql服务器的ip地址(默认<span class="hljs-number">127.0.0.1</span>) -P端口号(默认<span class="hljs-number">3306</span>)<br></code></pre></td></tr></table></figure>
<h4 id="退出">2.4.2 退出</h4>
<p>退出mysql：</p>
<figure class="highlight awk"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs awk"><span class="hljs-keyword">exit</span><br>quit<br></code></pre></td></tr></table></figure>
<h3 id="mysql数据模型">2.5 MySQL数据模型</h3>
<p><strong>关系型数据库：</strong></p>
<blockquote>
<p>关系型数据库是建立在关系模型基础上的数据库，简单说，关系型数据库是由多张能互相连接的
二维表 组成的数据库</p>
</blockquote>
<p>如下图，<code>订单信息表</code> 和 <code>客户信息表</code>
都是有行有列二维表我们将这样的称为关系型数据库。</p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163854797.png" srcset="/img/loading.gif" lazyload class="">
<p>接下来看关系型数据库的优点：</p>
<ul>
<li>都是使用表结构，格式一致，易于维护。</li>
<li>使用通用的 SQL 语言操作，使用方便，可用于复杂查询。
<ul>
<li>关系型数据库都可以通过SQL进行操作，所以使用方便。</li>
<li>复杂查询。现在需要查询001号订单数据，我们可以看到该订单是1号客户的订单，而1号订单是李聪这个客户。以后也可以在一张表中进行统计分析等操作。</li>
</ul></li>
<li>数据存储在磁盘中，安全。</li>
</ul>
<p><strong>数据模型：</strong></p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163903161.png" srcset="/img/loading.gif" lazyload class="">
<p>如上图，我们通过客户端可以通过数据库管理系统创建数据库，在数据库中创建表，在表中添加数据。创建的每一个数据库对应到磁盘上都是一个文件夹。比如可以通过SQL语句创建一个数据库（数据库名称为db1），语句如下。该语句咱们后面会学习。</p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163911161.png" srcset="/img/loading.gif" lazyload class="">
<p>我们可以在数据库安装目录下的data目录下看到多了一个 <code>db1</code>
的文件夹。所以，<strong>在MySQL中一个数据库对应到磁盘上的一个文件夹。</strong></p>
<p>而一个数据库下可以创建多张表，我们到MySQL中自带的mysql数据库的文件夹目录下：</p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163920284.png" srcset="/img/loading.gif" lazyload class="">
<p>而上图中右边的 <code>db.frm</code> 是表文件，<code>db.MYD</code>
是数据文件，通过这两个文件就可以查询到数据展示成二维表的效果。</p>
<p><strong>小结：</strong></p>
<ul>
<li>MySQL中可以创建多个数据库，每个数据库对应到磁盘上的一个文件夹</li>
<li>在每个数据库中可以创建多个表，每张都对应到磁盘上一个 frm 文件</li>
<li>每张表可以存储多条数据，数据会被存储到磁盘中 MYD 文件中</li>
</ul>
<h2 id="sql概述">3，SQL概述</h2>
<p>了解了数据模型后，接下来我们就学习SQL语句，通过SQL语句对数据库、表、数据进行增删改查操作。</p>
<h3 id="sql简介">3.1 SQL简介</h3>
<ul>
<li>英文：Structured Query Language，简称 SQL</li>
<li>结构化查询语言，一门操作关系型数据库的编程语言</li>
<li>定义操作所有关系型数据库的统一标准</li>
<li>对于同一个需求，每一种数据库操作的方式可能会存在一些不一样的地方，我们称为“方言”</li>
</ul>
<h3 id="通用语法">3.2 通用语法</h3>
<ul>
<li><p>SQL 语句可以单行或多行书写，以分号结尾。</p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163931228.png" srcset="/img/loading.gif" lazyload class="">
<p>如上，以分号结尾才是一个完整的sql语句。</p></li>
<li><p>MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写。</p>
<p>同样的一条sql语句写成下图的样子，一样可以运行处结果。</p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163941571.png" srcset="/img/loading.gif" lazyload class=""></li>
<li><p>注释</p>
<ul>
<li><p>单行注释: -- 注释内容 或 #注释内容(MySQL 特有)</p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163948376.png" srcset="/img/loading.gif" lazyload class="">
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505163956411.png" srcset="/img/loading.gif" lazyload class="">
<blockquote>
<p>注意：使用-- 添加单行注释时，--后面一定要加空格，而#没有要求。</p>
</blockquote></li>
<li><p>多行注释: /* 注释 */</p></li>
</ul></li>
</ul>
<h3 id="sql分类">3.3 SQL分类</h3>
<ul>
<li><p>DDL(Data Definition Language) ：
数据定义语言，用来定义数据库对象：数据库，表，列等</p>
<p>DDL简单理解就是用来操作数据库，表等</p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164004845.png" srcset="/img/loading.gif" lazyload class=""></li>
<li><p>DML(Data Manipulation Language)
数据操作语言，用来对数据库中表的数据进行增删改</p>
<p>DML简单理解就对表中数据进行增删改</p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164038977.png" srcset="/img/loading.gif" lazyload class=""></li>
<li><p>DQL(Data Query Language)
数据查询语言，用来查询数据库中表的记录(数据)</p>
<p>DQL简单理解就是对数据进行查询操作。从数据库表中查询到我们想要的数据。</p></li>
<li><p>DCL(Data Control Language)
数据控制语言，用来定义数据库的访问权限和安全级别，及创建用户</p>
<p>DML简单理解就是对数据库进行权限控制。比如我让某一个数据库表只能让某一个用户进行操作等。</p></li>
</ul>
<blockquote>
<p>注意： 以后我们最常操作的是 <code>DML</code> 和 <code>DQL</code>
，因为我们开发中最常操作的就是数据。</p>
</blockquote>
<h2 id="ddl操作数据库">4，DDL:操作数据库</h2>
<p>我们先来学习DDL来操作数据库。而操作数据库主要就是对数据库的增删查操作。</p>
<h3 id="查询">4.1 查询</h3>
<p>查询所有的数据库</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">SHOW</span> DATABASES;<br></code></pre></td></tr></table></figure>
<p>运行上面语句效果如下：</p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164101427.png" srcset="/img/loading.gif" lazyload class="">
<p>上述查询到的是的这些数据库是mysql安装好自带的数据库，我们以后不要操作这些数据库。</p>
<h3 id="创建数据库">4.2 创建数据库</h3>
<ul>
<li><strong>创建数据库</strong>：</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> DATABASE 数据库名称;<br></code></pre></td></tr></table></figure>
<p>运行语句效果如下：</p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164122654.png" srcset="/img/loading.gif" lazyload class="">
<p>而在创建数据库的时候，我并不知道db1数据库有没有创建，直接再次创建名为db1的数据库就会出现错误。</p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164132470.png" srcset="/img/loading.gif" lazyload class="">
<p>为了避免上面的错误，在创建数据库的时候先做判断，如果不存在再创建。</p>
<ul>
<li><strong>创建数据库(判断，如果不存在则创建)</strong></li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> DATABASE IF <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">EXISTS</span> 数据库名称;<br></code></pre></td></tr></table></figure>
<p>运行语句效果如下：</p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164140046.png" srcset="/img/loading.gif" lazyload class="">
<p>从上面的效果可以看到虽然db1数据库已经存在，再创建db1也没有报错，而创建db2数据库则创建成功。</p>
<h3 id="删除数据库">4.3 删除数据库</h3>
<ul>
<li><strong>删除数据库</strong></li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">DROP</span> DATABASE 数据库名称;<br></code></pre></td></tr></table></figure>
<ul>
<li><strong>删除数据库(判断，如果存在则删除)</strong></li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">DROP</span> DATABASE IF <span class="hljs-keyword">EXISTS</span> 数据库名称;<br></code></pre></td></tr></table></figure>
<p>运行语句效果如下：</p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164151158.png" srcset="/img/loading.gif" lazyload class="">
<h3 id="使用数据库">4.4 使用数据库</h3>
<p>数据库创建好了，要在数据库中创建表，得先明确在哪儿个数据库中操作，此时就需要使用数据库。</p>
<ul>
<li><strong>使用数据库</strong></li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql">USE 数据库名称;<br></code></pre></td></tr></table></figure>
<ul>
<li><strong>查看当前使用的数据库</strong></li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> DATABASE();<br></code></pre></td></tr></table></figure>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164208464.png" srcset="/img/loading.gif" lazyload class="">
<h2 id="ddl操作表">5，DDL:操作表</h2>
<p>操作表也就是对表进行增（Create）删（Retrieve）改（Update）查（Delete）。</p>
<h3 id="查询表">5.1 查询表</h3>
<ul>
<li><strong>查询当前数据库下所有表名称</strong></li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">SHOW</span> TABLES;<br></code></pre></td></tr></table></figure>
<p>我们创建的数据库中没有任何表，因此我们进入mysql自带的mysql数据库，执行上述语句查看</p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164401008.png" srcset="/img/loading.gif" lazyload class="">
<ul>
<li><strong>查询表结构</strong></li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">DESC</span> 表名称;<br></code></pre></td></tr></table></figure>
<p>查看mysql数据库中func表的结构，运行语句如下：</p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164412987.png" srcset="/img/loading.gif" lazyload class="">
<h3 id="创建表">5.2 创建表</h3>
<ul>
<li><strong>创建表</strong></li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> 表名 (<br>	字段名<span class="hljs-number">1</span>  数据类型<span class="hljs-number">1</span>,<br>	字段名<span class="hljs-number">2</span>  数据类型<span class="hljs-number">2</span>,<br>	…<br>	字段名n  数据类型n<br>);<br><br></code></pre></td></tr></table></figure>
<blockquote>
<p>注意：最后一行末尾，不能加逗号</p>
</blockquote>
<p>知道了创建表的语句，那么我们创建创建如下结构的表</p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164429919.png" srcset="/img/loading.gif" lazyload class="">
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> tb_user (<br>	id <span class="hljs-type">int</span>,<br>    username <span class="hljs-type">varchar</span>(<span class="hljs-number">20</span>),<br>    password <span class="hljs-type">varchar</span>(<span class="hljs-number">32</span>)<br>);<br></code></pre></td></tr></table></figure>
<p>运行语句如下：</p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164436618.png" srcset="/img/loading.gif" lazyload class="">
<h3 id="数据类型">5.3 数据类型</h3>
<p>MySQL 支持多种类型，可以分为三类：</p>
<ul>
<li><p>数值</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><code class="hljs sql">tinyint : 小整数型，占一个字节<br><span class="hljs-type">int</span>	： 大整数类型，占四个字节<br>	eg ： age <span class="hljs-type">int</span><br><span class="hljs-keyword">double</span> ： 浮点类型<br>	使用格式： 字段名 <span class="hljs-keyword">double</span>(总长度,小数点后保留的位数)<br>	eg ： score <span class="hljs-keyword">double</span>(<span class="hljs-number">5</span>,<span class="hljs-number">2</span>)   <br></code></pre></td></tr></table></figure></li>
<li><p>日期</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-type">date</span> ： 日期值。只包含年月日<br>	eg ：birthday <span class="hljs-type">date</span> ： <br>datetime ： 混合日期和时间值。包含年月日时分秒<br></code></pre></td></tr></table></figure></li>
<li><p>字符串</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-type">char</span> ： 定长字符串。<br>	优点：存储性能高<br>	缺点：浪费空间<br>	eg ： name <span class="hljs-type">char</span>(<span class="hljs-number">10</span>)  如果存储的数据字符个数不足<span class="hljs-number">10</span>个，也会占<span class="hljs-number">10</span>个的空间<br><span class="hljs-type">varchar</span> ： 变长字符串。<br>	优点：节约空间<br>	缺点：存储性能底<br>	eg ： name <span class="hljs-type">varchar</span>(<span class="hljs-number">10</span>) 如果存储的数据字符个数不足<span class="hljs-number">10</span>个，那就数据字符个数是几就占几个的空间	<br></code></pre></td></tr></table></figure></li>
</ul>
<blockquote>
<p>注意：其他类型参考资料中的《MySQL数据类型].xlsx》</p>
</blockquote>
<p><strong>案例：</strong></p>
<figure class="highlight markdown"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><code class="hljs markdown">需求：设计一张学生表，请注重数据类型、长度的合理性<br><span class="hljs-bullet">	1.</span> 编号<br><span class="hljs-bullet">	2.</span> 姓名，姓名最长不超过10个汉字<br><span class="hljs-bullet">	3.</span> 性别，因为取值只有两种可能，因此最多一个汉字<br><span class="hljs-bullet">	4.</span> 生日，取值为年月日<br><span class="hljs-bullet">	5.</span> 入学成绩，小数点后保留两位<br><span class="hljs-bullet">	6.</span> 邮件地址，最大长度不超过 64<br><span class="hljs-bullet">	7.</span> 家庭联系电话，不一定是手机号码，可能会出现 - 等字符<br><span class="hljs-bullet">	8.</span> 学生状态（用数字表示，正常、休学、毕业...）<br></code></pre></td></tr></table></figure>
<p>语句设计如下：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> student (<br>	id <span class="hljs-type">int</span>,<br>    name <span class="hljs-type">varchar</span>(<span class="hljs-number">10</span>),<br>    gender <span class="hljs-type">char</span>(<span class="hljs-number">1</span>),<br>    birthday <span class="hljs-type">date</span>,<br>    score <span class="hljs-keyword">double</span>(<span class="hljs-number">5</span>,<span class="hljs-number">2</span>),<br>    email <span class="hljs-type">varchar</span>(<span class="hljs-number">15</span>),<br>    tel <span class="hljs-type">varchar</span>(<span class="hljs-number">15</span>),<br>    status tinyint<br>);<br></code></pre></td></tr></table></figure>
<h3 id="删除表">5.4 删除表</h3>
<ul>
<li><strong>删除表</strong></li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">DROP</span> <span class="hljs-keyword">TABLE</span> 表名;<br></code></pre></td></tr></table></figure>
<ul>
<li><strong>删除表时判断表是否存在</strong></li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">DROP</span> <span class="hljs-keyword">TABLE</span> IF <span class="hljs-keyword">EXISTS</span> 表名;<br></code></pre></td></tr></table></figure>
<p>运行语句效果如下：</p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164449264.png" srcset="/img/loading.gif" lazyload class="">
<h3 id="修改表">5.5 修改表</h3>
<ul>
<li><strong>修改表名</strong></li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">ALTER</span> <span class="hljs-keyword">TABLE</span> 表名 RENAME <span class="hljs-keyword">TO</span> 新的表名;<br><br><span class="hljs-comment">-- 将表名student修改为stu</span><br><span class="hljs-keyword">alter</span> <span class="hljs-keyword">table</span> student rename <span class="hljs-keyword">to</span> stu;<br></code></pre></td></tr></table></figure>
<ul>
<li><strong>添加一列</strong></li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">ALTER</span> <span class="hljs-keyword">TABLE</span> 表名 <span class="hljs-keyword">ADD</span> 列名 数据类型;<br><br><span class="hljs-comment">-- 给stu表添加一列address，该字段类型是varchar(50)</span><br><span class="hljs-keyword">alter</span> <span class="hljs-keyword">table</span> stu <span class="hljs-keyword">add</span> address <span class="hljs-type">varchar</span>(<span class="hljs-number">50</span>);<br></code></pre></td></tr></table></figure>
<ul>
<li><strong>修改数据类型</strong></li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">ALTER</span> <span class="hljs-keyword">TABLE</span> 表名 MODIFY 列名 新数据类型;<br><br><span class="hljs-comment">-- 将stu表中的address字段的类型改为 char(50)</span><br><span class="hljs-keyword">alter</span> <span class="hljs-keyword">table</span> stu modify address <span class="hljs-type">char</span>(<span class="hljs-number">50</span>);<br></code></pre></td></tr></table></figure>
<ul>
<li><strong>修改列名和数据类型</strong></li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">ALTER</span> <span class="hljs-keyword">TABLE</span> 表名 CHANGE 列名 新列名 新数据类型;<br><br><span class="hljs-comment">-- 将stu表中的address字段名改为 addr，类型改为varchar(50)</span><br><span class="hljs-keyword">alter</span> <span class="hljs-keyword">table</span> stu change address addr <span class="hljs-type">varchar</span>(<span class="hljs-number">50</span>);<br></code></pre></td></tr></table></figure>
<ul>
<li><strong>删除列</strong></li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">ALTER</span> <span class="hljs-keyword">TABLE</span> 表名 <span class="hljs-keyword">DROP</span> 列名;<br><br><span class="hljs-comment">-- 将stu表中的addr字段 删除</span><br><span class="hljs-keyword">alter</span> <span class="hljs-keyword">table</span> stu <span class="hljs-keyword">drop</span> addr;<br></code></pre></td></tr></table></figure>
<h2 id="navicat使用">6，navicat使用</h2>
<p>通过上面的学习，我们发现在命令行中写sql语句特别不方便，尤其是编写创建表的语句，我们只能在记事本上写好后直接复制到命令行进行执行。那么有没有刚好的工具提供给我们进行使用呢？
有。</p>
<h3 id="navicat概述">6.1 navicat概述</h3>
<ul>
<li>Navicat for MySQL 是管理和开发 MySQL 或 MariaDB
的理想解决方案。</li>
<li>这套全面的前端工具为数据库管理、开发和维护提供了一款直观而强大的图形界面。</li>
<li>官网： <a
target="_blank" rel="noopener" href="http://www.navicat.com.cn/">http://www.navicat.com.cn</a></li>
</ul>
<h3 id="navicat安装">6.2 navicat安装</h3>
<p>参考 : 资料_mysql_x86.md</p>
<h3 id="navicat使用-1">6.3 navicat使用</h3>
<h4 id="建立和mysql服务的连接">6.3.1 建立和mysql服务的连接</h4>
<p>第一步： 点击连接，选择MySQL</p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164501098.png" srcset="/img/loading.gif" lazyload class="">
<p>第二步：填写连接数据库必要的信息</p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164507470.png" srcset="/img/loading.gif" lazyload class="">
<p>以上操作没有问题就会出现如下图所示界面：</p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164518873.png" srcset="/img/loading.gif" lazyload class="">
<h4 id="操作">6.3.2 操作</h4>
<p>连接成功后就能看到如下图界面：</p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164620069.png" srcset="/img/loading.gif" lazyload class="">
<ul>
<li><strong>修改表结构</strong></li>
</ul>
<p>通过下图操作修改表结构：</p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164720665.png" srcset="/img/loading.gif" lazyload class="">
<p>点击了设计表后即出现如下图所示界面，在图中红框中直接修改字段名，类型等信息：</p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164728112.png" srcset="/img/loading.gif" lazyload class="">
<ul>
<li><strong>编写SQL语句并执行</strong></li>
</ul>
<p>按照如下图所示进行操作即可书写SQL语句并执行sql语句。</p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164737419.png" srcset="/img/loading.gif" lazyload class="">
<h2 id="dml">7，DML</h2>
<p>DML主要是对数据进行增（insert）删（delete）改（update）操作。</p>
<h3 id="添加数据">7.1 添加数据</h3>
<ul>
<li><strong>给指定列添加数据</strong></li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> 表名(列名<span class="hljs-number">1</span>,列名<span class="hljs-number">2</span>,…) <span class="hljs-keyword">VALUES</span>(值<span class="hljs-number">1</span>,值<span class="hljs-number">2</span>,…);<br></code></pre></td></tr></table></figure>
<ul>
<li><strong>给全部列添加数据</strong></li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> 表名 <span class="hljs-keyword">VALUES</span>(值<span class="hljs-number">1</span>,值<span class="hljs-number">2</span>,…);<br></code></pre></td></tr></table></figure>
<ul>
<li><strong>批量添加数据</strong></li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> 表名(列名<span class="hljs-number">1</span>,列名<span class="hljs-number">2</span>,…) <span class="hljs-keyword">VALUES</span>(值<span class="hljs-number">1</span>,值<span class="hljs-number">2</span>,…),(值<span class="hljs-number">1</span>,值<span class="hljs-number">2</span>,…),(值<span class="hljs-number">1</span>,值<span class="hljs-number">2</span>,…)…;<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> 表名 <span class="hljs-keyword">VALUES</span>(值<span class="hljs-number">1</span>,值<span class="hljs-number">2</span>,…),(值<span class="hljs-number">1</span>,值<span class="hljs-number">2</span>,…),(值<span class="hljs-number">1</span>,值<span class="hljs-number">2</span>,…)…;<br></code></pre></td></tr></table></figure>
<ul>
<li><strong>练习</strong></li>
</ul>
<p>为了演示以下的增删改操作是否操作成功，故先将查询所有数据的语句介绍给大家：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">select</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> stu;<br></code></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-comment">-- 给指定列添加数据</span><br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> stu (id, NAME) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">1</span>, <span class="hljs-string">&#x27;张三&#x27;</span>);<br><span class="hljs-comment">-- 给所有列添加数据，列名的列表可以省略的</span><br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> stu <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">2</span>,<span class="hljs-string">&#x27;李四&#x27;</span>,<span class="hljs-string">&#x27;男&#x27;</span>,<span class="hljs-string">&#x27;1999-11-11&#x27;</span>,<span class="hljs-number">88.88</span>,<span class="hljs-string">&#x27;lisi@itcast.cn&#x27;</span>,<span class="hljs-string">&#x27;13888888888&#x27;</span>,<span class="hljs-number">1</span>);<br><br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> stu <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">2</span>,<span class="hljs-string">&#x27;李四&#x27;</span>,<span class="hljs-string">&#x27;男&#x27;</span>,<span class="hljs-string">&#x27;1999-11-11&#x27;</span>,<span class="hljs-number">88.88</span>,<span class="hljs-string">&#x27;lisi@itcast.cn&#x27;</span>,<span class="hljs-string">&#x27;13888888888&#x27;</span>,<span class="hljs-number">1</span>);<br><br><span class="hljs-comment">-- 批量添加数据</span><br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> stu <span class="hljs-keyword">VALUES</span> <br>	(<span class="hljs-number">2</span>,<span class="hljs-string">&#x27;李四&#x27;</span>,<span class="hljs-string">&#x27;男&#x27;</span>,<span class="hljs-string">&#x27;1999-11-11&#x27;</span>,<span class="hljs-number">88.88</span>,<span class="hljs-string">&#x27;lisi@itcast.cn&#x27;</span>,<span class="hljs-string">&#x27;13888888888&#x27;</span>,<span class="hljs-number">1</span>),<br>	(<span class="hljs-number">2</span>,<span class="hljs-string">&#x27;李四&#x27;</span>,<span class="hljs-string">&#x27;男&#x27;</span>,<span class="hljs-string">&#x27;1999-11-11&#x27;</span>,<span class="hljs-number">88.88</span>,<span class="hljs-string">&#x27;lisi@itcast.cn&#x27;</span>,<span class="hljs-string">&#x27;13888888888&#x27;</span>,<span class="hljs-number">1</span>),<br>	(<span class="hljs-number">2</span>,<span class="hljs-string">&#x27;李四&#x27;</span>,<span class="hljs-string">&#x27;男&#x27;</span>,<span class="hljs-string">&#x27;1999-11-11&#x27;</span>,<span class="hljs-number">88.88</span>,<span class="hljs-string">&#x27;lisi@itcast.cn&#x27;</span>,<span class="hljs-string">&#x27;13888888888&#x27;</span>,<span class="hljs-number">1</span>);<br></code></pre></td></tr></table></figure>
<h3 id="修改数据">7.2 修改数据</h3>
<ul>
<li><strong>修改表数据</strong></li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">UPDATE</span> 表名 <span class="hljs-keyword">SET</span> 列名<span class="hljs-number">1</span><span class="hljs-operator">=</span>值<span class="hljs-number">1</span>,列名<span class="hljs-number">2</span><span class="hljs-operator">=</span>值<span class="hljs-number">2</span>,… [<span class="hljs-keyword">WHERE</span> 条件] ;<br></code></pre></td></tr></table></figure>
<blockquote>
<p>注意：</p>
<ol type="1">
<li>修改语句中如果不加条件，则将所有数据都修改！</li>
<li>像上面的语句中的中括号，表示在写sql语句中可以省略这部分</li>
</ol>
</blockquote>
<ul>
<li><p><strong>练习</strong></p>
<ul>
<li>将张三的性别改为女</li>
</ul>
<p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">update</span> stu <span class="hljs-keyword">set</span> sex <span class="hljs-operator">=</span> <span class="hljs-string">&#x27;女&#x27;</span> <span class="hljs-keyword">where</span> name <span class="hljs-operator">=</span> <span class="hljs-string">&#x27;张三&#x27;</span>;<br></code></pre></td></tr></table></figure></p>
<ul>
<li><p>将张三的生日改为 1999-12-12 分数改为99.99</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">update</span> stu <span class="hljs-keyword">set</span> birthday <span class="hljs-operator">=</span> <span class="hljs-string">&#x27;1999-12-12&#x27;</span>, score <span class="hljs-operator">=</span> <span class="hljs-number">99.99</span> <span class="hljs-keyword">where</span> name <span class="hljs-operator">=</span> <span class="hljs-string">&#x27;张三&#x27;</span>;<br></code></pre></td></tr></table></figure></li>
<li><p>注意：如果update语句没有加where条件，则会将表中所有数据全部修改！</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">update</span> stu <span class="hljs-keyword">set</span> sex <span class="hljs-operator">=</span> <span class="hljs-string">&#x27;女&#x27;</span>;<br></code></pre></td></tr></table></figure>
<p>上面语句的执行完后查询到的结果是：</p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164759177.png" srcset="/img/loading.gif" lazyload class=""></li>
</ul></li>
</ul>
<h3 id="删除数据">7.3 删除数据</h3>
<ul>
<li><strong>删除数据</strong></li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">DELETE</span> <span class="hljs-keyword">FROM</span> 表名 [<span class="hljs-keyword">WHERE</span> 条件] ;<br></code></pre></td></tr></table></figure>
<ul>
<li><strong>练习</strong></li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-comment">-- 删除张三记录</span><br><span class="hljs-keyword">delete</span> <span class="hljs-keyword">from</span> stu <span class="hljs-keyword">where</span> name <span class="hljs-operator">=</span> <span class="hljs-string">&#x27;张三&#x27;</span>;<br><br><span class="hljs-comment">-- 删除stu表中所有的数据</span><br><span class="hljs-keyword">delete</span> <span class="hljs-keyword">from</span> stu;<br></code></pre></td></tr></table></figure>
<h2 id="dql">8，DQL</h2>
<p>下面是黑马程序员展示试题库数据的页面</p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164811149.png" srcset="/img/loading.gif" lazyload class="">
<p>页面上展示的数据肯定是在数据库中的试题库表中进行存储，而我们需要将数据库中的数据查询出来并展示在页面给用户看。上图中的是最基本的查询效果，那么数据库其实是很多的，不可能在将所有的数据在一页进行全部展示，而页面上会有分页展示的效果，如下：</p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164820312.png" srcset="/img/loading.gif" lazyload class="">
<p>当然上图中的难度字段当我们点击也可以实现排序查询操作。从这个例子我们就可以看出，对于数据库的查询时灵活多变的，需要根据具体的需求来实现，而数据库查询操作也是最重要的操作，所以此部分需要大家重点掌握。</p>
<p>接下来我们先介绍查询的完整语法：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> <br>    字段列表<br><span class="hljs-keyword">FROM</span> <br>    表名列表 <br><span class="hljs-keyword">WHERE</span> <br>    条件列表<br><span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span><br>    分组字段<br><span class="hljs-keyword">HAVING</span><br>    分组后条件<br><span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span><br>    排序字段<br>LIMIT<br>    分页限定<br></code></pre></td></tr></table></figure>
<p>为了给大家演示查询的语句，我们需要先准备表及一些数据：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-comment">-- 删除stu表</span><br><span class="hljs-keyword">drop</span> <span class="hljs-keyword">table</span> if <span class="hljs-keyword">exists</span> stu;<br><br><br><span class="hljs-comment">-- 创建stu表</span><br><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> stu (<br> id <span class="hljs-type">int</span>, <span class="hljs-comment">-- 编号</span><br> name <span class="hljs-type">varchar</span>(<span class="hljs-number">20</span>), <span class="hljs-comment">-- 姓名</span><br> age <span class="hljs-type">int</span>, <span class="hljs-comment">-- 年龄</span><br> sex <span class="hljs-type">varchar</span>(<span class="hljs-number">5</span>), <span class="hljs-comment">-- 性别</span><br> address <span class="hljs-type">varchar</span>(<span class="hljs-number">100</span>), <span class="hljs-comment">-- 地址</span><br> math <span class="hljs-keyword">double</span>(<span class="hljs-number">5</span>,<span class="hljs-number">2</span>), <span class="hljs-comment">-- 数学成绩</span><br> english <span class="hljs-keyword">double</span>(<span class="hljs-number">5</span>,<span class="hljs-number">2</span>), <span class="hljs-comment">-- 英语成绩</span><br> hire_date <span class="hljs-type">date</span> <span class="hljs-comment">-- 入学时间</span><br>);<br><br><span class="hljs-comment">-- 添加数据</span><br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> stu(id,NAME,age,sex,address,math,english,hire_date) <br><span class="hljs-keyword">VALUES</span> <br>(<span class="hljs-number">1</span>,<span class="hljs-string">&#x27;马运&#x27;</span>,<span class="hljs-number">55</span>,<span class="hljs-string">&#x27;男&#x27;</span>,<span class="hljs-string">&#x27;杭州&#x27;</span>,<span class="hljs-number">66</span>,<span class="hljs-number">78</span>,<span class="hljs-string">&#x27;1995-09-01&#x27;</span>),<br>(<span class="hljs-number">2</span>,<span class="hljs-string">&#x27;马花疼&#x27;</span>,<span class="hljs-number">45</span>,<span class="hljs-string">&#x27;女&#x27;</span>,<span class="hljs-string">&#x27;深圳&#x27;</span>,<span class="hljs-number">98</span>,<span class="hljs-number">87</span>,<span class="hljs-string">&#x27;1998-09-01&#x27;</span>),<br>(<span class="hljs-number">3</span>,<span class="hljs-string">&#x27;马斯克&#x27;</span>,<span class="hljs-number">55</span>,<span class="hljs-string">&#x27;男&#x27;</span>,<span class="hljs-string">&#x27;香港&#x27;</span>,<span class="hljs-number">56</span>,<span class="hljs-number">77</span>,<span class="hljs-string">&#x27;1999-09-02&#x27;</span>),<br>(<span class="hljs-number">4</span>,<span class="hljs-string">&#x27;柳白&#x27;</span>,<span class="hljs-number">20</span>,<span class="hljs-string">&#x27;女&#x27;</span>,<span class="hljs-string">&#x27;湖南&#x27;</span>,<span class="hljs-number">76</span>,<span class="hljs-number">65</span>,<span class="hljs-string">&#x27;1997-09-05&#x27;</span>),<br>(<span class="hljs-number">5</span>,<span class="hljs-string">&#x27;柳青&#x27;</span>,<span class="hljs-number">20</span>,<span class="hljs-string">&#x27;男&#x27;</span>,<span class="hljs-string">&#x27;湖南&#x27;</span>,<span class="hljs-number">86</span>,<span class="hljs-keyword">NULL</span>,<span class="hljs-string">&#x27;1998-09-01&#x27;</span>),<br>(<span class="hljs-number">6</span>,<span class="hljs-string">&#x27;刘德花&#x27;</span>,<span class="hljs-number">57</span>,<span class="hljs-string">&#x27;男&#x27;</span>,<span class="hljs-string">&#x27;香港&#x27;</span>,<span class="hljs-number">99</span>,<span class="hljs-number">99</span>,<span class="hljs-string">&#x27;1998-09-01&#x27;</span>),<br>(<span class="hljs-number">7</span>,<span class="hljs-string">&#x27;张学右&#x27;</span>,<span class="hljs-number">22</span>,<span class="hljs-string">&#x27;女&#x27;</span>,<span class="hljs-string">&#x27;香港&#x27;</span>,<span class="hljs-number">99</span>,<span class="hljs-number">99</span>,<span class="hljs-string">&#x27;1998-09-01&#x27;</span>),<br>(<span class="hljs-number">8</span>,<span class="hljs-string">&#x27;德玛西亚&#x27;</span>,<span class="hljs-number">18</span>,<span class="hljs-string">&#x27;男&#x27;</span>,<span class="hljs-string">&#x27;南京&#x27;</span>,<span class="hljs-number">56</span>,<span class="hljs-number">65</span>,<span class="hljs-string">&#x27;1994-09-02&#x27;</span>);<br></code></pre></td></tr></table></figure>
<p>接下来咱们从最基本的查询语句开始学起。</p>
<h3 id="基础查询">8.1 基础查询</h3>
<h4 id="语法">8.1.1 语法</h4>
<ul>
<li><strong>查询多个字段</strong></li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> 字段列表 <span class="hljs-keyword">FROM</span> 表名;<br><span class="hljs-keyword">SELECT</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">FROM</span> 表名; <span class="hljs-comment">-- 查询所有数据</span><br></code></pre></td></tr></table></figure>
<ul>
<li><strong>去除重复记录</strong></li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">DISTINCT</span> 字段列表 <span class="hljs-keyword">FROM</span> 表名;<br></code></pre></td></tr></table></figure>
<ul>
<li><strong>起别名</strong></li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">AS</span>: <span class="hljs-keyword">AS</span> 也可以省略<br></code></pre></td></tr></table></figure>
<h4 id="练习">8.1.2 练习</h4>
<ul>
<li><p>查询name、age两列</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">select</span> name,age <span class="hljs-keyword">from</span> stu;<br></code></pre></td></tr></table></figure></li>
<li><p>查询所有列的数据，列名的列表可以使用*替代</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">select</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> stu;<br></code></pre></td></tr></table></figure>
<p>上面语句中的*不建议大家使用，因为在这写*不方便我们阅读sql语句。我们写字段列表的话，可以添加注释对每一个字段进行说明</p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164846978.png" srcset="/img/loading.gif" lazyload class="">
<p>而在上课期间为了简约课程的时间，老师很多地方都会写*。</p></li>
<li><p>查询地址信息</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">select</span> address <span class="hljs-keyword">from</span> stu;<br></code></pre></td></tr></table></figure>
<p>执行上面语句结果如下：</p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164853885.png" srcset="/img/loading.gif" lazyload class="">
<p>从上面的结果我们可以看到有重复的数据，我们也可以使用
<code>distinct</code> 关键字去重重复数据。</p></li>
<li><p>去除重复记录</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">select</span> <span class="hljs-keyword">distinct</span> address <span class="hljs-keyword">from</span> stu;<br></code></pre></td></tr></table></figure></li>
<li><p>查询姓名、数学成绩、英语成绩。并通过as给math和english起别名（as关键字可以省略）</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">select</span> name,math <span class="hljs-keyword">as</span> 数学成绩,english <span class="hljs-keyword">as</span> 英文成绩 <span class="hljs-keyword">from</span> stu;<br><span class="hljs-keyword">select</span> name,math 数学成绩,english 英文成绩 <span class="hljs-keyword">from</span> stu;<br></code></pre></td></tr></table></figure></li>
</ul>
<h3 id="条件查询">8.2 条件查询</h3>
<h4 id="语法-1">8.2.1 语法</h4>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> 字段列表 <span class="hljs-keyword">FROM</span> 表名 <span class="hljs-keyword">WHERE</span> 条件列表;<br></code></pre></td></tr></table></figure>
<ul>
<li><strong>条件</strong></li>
</ul>
<p>条件列表可以使用以下运算符</p>
<p><img src="assets/image-20210722190508272.png" srcset="/img/loading.gif" lazyload alt="image-20210722190508272" style="zoom:60%;" /></p>
<h4 id="条件查询练习">8.2.2 条件查询练习</h4>
<ul>
<li><p>查询年龄大于20岁的学员信息</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">select</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> stu <span class="hljs-keyword">where</span> age <span class="hljs-operator">&gt;</span> <span class="hljs-number">20</span>;<br></code></pre></td></tr></table></figure></li>
<li><p>查询年龄大于等于20岁的学员信息</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">select</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> stu <span class="hljs-keyword">where</span> age <span class="hljs-operator">&gt;=</span> <span class="hljs-number">20</span>;<br></code></pre></td></tr></table></figure></li>
<li><p>查询年龄大于等于20岁 并且 年龄 小于等于 30岁 的学员信息</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">select</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> stu <span class="hljs-keyword">where</span> age <span class="hljs-operator">&gt;=</span> <span class="hljs-number">20</span> <span class="hljs-operator">&amp;&amp;</span>  age <span class="hljs-operator">&lt;=</span> <span class="hljs-number">30</span>;<br><span class="hljs-keyword">select</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> stu <span class="hljs-keyword">where</span> age <span class="hljs-operator">&gt;=</span> <span class="hljs-number">20</span> <span class="hljs-keyword">and</span>  age <span class="hljs-operator">&lt;=</span> <span class="hljs-number">30</span>;<br></code></pre></td></tr></table></figure>
<blockquote>
<p>上面语句中 &amp;&amp; 和 and 都表示并且的意思。建议使用 and 。</p>
<p>也可以使用 between ... and 来实现上面需求</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">select</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> stu <span class="hljs-keyword">where</span> age <span class="hljs-keyword">BETWEEN</span> <span class="hljs-number">20</span> <span class="hljs-keyword">and</span> <span class="hljs-number">30</span>;<br></code></pre></td></tr></table></figure></li>
<li><p>查询入学日期在'1998-09-01' 到 '1999-09-01' 之间的学员信息</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">select</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> stu <span class="hljs-keyword">where</span> hire_date <span class="hljs-keyword">BETWEEN</span> <span class="hljs-string">&#x27;1998-09-01&#x27;</span> <span class="hljs-keyword">and</span> <span class="hljs-string">&#x27;1999-09-01&#x27;</span>;<br></code></pre></td></tr></table></figure></li>
<li><p>查询年龄等于18岁的学员信息</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">select</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> stu <span class="hljs-keyword">where</span> age <span class="hljs-operator">=</span> <span class="hljs-number">18</span>;<br></code></pre></td></tr></table></figure></li>
<li><p>查询年龄不等于18岁的学员信息</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">select</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> stu <span class="hljs-keyword">where</span> age <span class="hljs-operator">!=</span> <span class="hljs-number">18</span>;<br><span class="hljs-keyword">select</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> stu <span class="hljs-keyword">where</span> age <span class="hljs-operator">&lt;&gt;</span> <span class="hljs-number">18</span>;<br></code></pre></td></tr></table></figure></li>
<li><p>查询年龄等于18岁 或者 年龄等于20岁 或者
年龄等于22岁的学员信息</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">select</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> stu <span class="hljs-keyword">where</span> age <span class="hljs-operator">=</span> <span class="hljs-number">18</span> <span class="hljs-keyword">or</span> age <span class="hljs-operator">=</span> <span class="hljs-number">20</span> <span class="hljs-keyword">or</span> age <span class="hljs-operator">=</span> <span class="hljs-number">22</span>;<br><span class="hljs-keyword">select</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> stu <span class="hljs-keyword">where</span> age <span class="hljs-keyword">in</span> (<span class="hljs-number">18</span>,<span class="hljs-number">20</span> ,<span class="hljs-number">22</span>);<br></code></pre></td></tr></table></figure></li>
<li><p>查询英语成绩为 null的学员信息</p>
<p>null值的比较不能使用 = 或者 != 。需要使用 is 或者 is not</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">select</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> stu <span class="hljs-keyword">where</span> english <span class="hljs-operator">=</span> <span class="hljs-keyword">null</span>; <span class="hljs-comment">-- 这个语句是不行的</span><br><span class="hljs-keyword">select</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> stu <span class="hljs-keyword">where</span> english <span class="hljs-keyword">is</span> <span class="hljs-keyword">null</span>;<br><span class="hljs-keyword">select</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> stu <span class="hljs-keyword">where</span> english <span class="hljs-keyword">is</span> <span class="hljs-keyword">not</span> <span class="hljs-keyword">null</span>;<br></code></pre></td></tr></table></figure></li>
</ul>
<h4 id="模糊查询练习">8.2.3 模糊查询练习</h4>
<blockquote>
<p>模糊查询使用like关键字，可以使用通配符进行占位:</p>
<p>（1）_ : 代表单个任意字符</p>
<p>（2）% : 代表任意个数字符</p>
</blockquote>
<ul>
<li><p>查询姓'马'的学员信息</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">select</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> stu <span class="hljs-keyword">where</span> name <span class="hljs-keyword">like</span> <span class="hljs-string">&#x27;马%&#x27;</span>;<br></code></pre></td></tr></table></figure></li>
<li><p>查询第二个字是'花'的学员信息</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">select</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> stu <span class="hljs-keyword">where</span> name <span class="hljs-keyword">like</span> <span class="hljs-string">&#x27;_花%&#x27;</span>;<br></code></pre></td></tr></table></figure></li>
<li><p>查询名字中包含 '德' 的学员信息</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">select</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> stu <span class="hljs-keyword">where</span> name <span class="hljs-keyword">like</span> <span class="hljs-string">&#x27;%德%&#x27;</span>;<br></code></pre></td></tr></table></figure></li>
</ul>
<h3 id="排序查询">8.3 排序查询</h3>
<h4 id="语法-2">8.3.1 语法</h4>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> 字段列表 <span class="hljs-keyword">FROM</span> 表名 <span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> 排序字段名<span class="hljs-number">1</span> [排序方式<span class="hljs-number">1</span>],排序字段名<span class="hljs-number">2</span> [排序方式<span class="hljs-number">2</span>] …;<br></code></pre></td></tr></table></figure>
<p>上述语句中的排序方式有两种，分别是：</p>
<ul>
<li>ASC ： 升序排列 <strong>（默认值）</strong></li>
<li>DESC ： 降序排列</li>
</ul>
<blockquote>
<p>注意：如果有多个排序条件，当前边的条件值一样时，才会根据第二条件进行排序</p>
</blockquote>
<h4 id="练习-1">8.3.2 练习</h4>
<ul>
<li><p>查询学生信息，按照年龄升序排列</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">select</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> stu <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> age ;<br></code></pre></td></tr></table></figure></li>
<li><p>查询学生信息，按照数学成绩降序排列</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">select</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> stu <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> math <span class="hljs-keyword">desc</span> ;<br></code></pre></td></tr></table></figure></li>
<li><p>查询学生信息，按照数学成绩降序排列，如果数学成绩一样，再按照英语成绩升序排列</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">select</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> stu <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> math <span class="hljs-keyword">desc</span> , english <span class="hljs-keyword">asc</span> ;<br></code></pre></td></tr></table></figure></li>
</ul>
<h3 id="聚合函数">8.4 聚合函数</h3>
<h4 id="概念">8.4.1 概念</h4>
<p>==将一列数据作为一个整体，进行纵向计算。==</p>
<p>如何理解呢？假设有如下表</p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164912745.png" srcset="/img/loading.gif" lazyload class="">
<p>现有一需求让我们求表中所有数据的数学成绩的总和。这就是对math字段进行纵向求和。</p>
<h4 id="聚合函数分类">8.4.2 聚合函数分类</h4>
<table>
<thead>
<tr class="header">
<th>函数名</th>
<th>功能</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td>count(列名)</td>
<td>统计数量（一般选用不为null的列）</td>
</tr>
<tr class="even">
<td>max(列名)</td>
<td>最大值</td>
</tr>
<tr class="odd">
<td>min(列名)</td>
<td>最小值</td>
</tr>
<tr class="even">
<td>sum(列名)</td>
<td>求和</td>
</tr>
<tr class="odd">
<td>avg(列名)</td>
<td>平均值</td>
</tr>
</tbody>
</table>
<h4 id="聚合函数语法">8.4.3 聚合函数语法</h4>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> 聚合函数名(列名) <span class="hljs-keyword">FROM</span> 表;<br></code></pre></td></tr></table></figure>
<blockquote>
<p>注意：null 值不参与所有聚合函数运算</p>
</blockquote>
<h4 id="练习-2">8.4.4 练习</h4>
<ul>
<li><p>统计班级一共有多少个学生</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">select</span> <span class="hljs-built_in">count</span>(id) <span class="hljs-keyword">from</span> stu;<br><span class="hljs-keyword">select</span> <span class="hljs-built_in">count</span>(english) <span class="hljs-keyword">from</span> stu;<br></code></pre></td></tr></table></figure>
<p>上面语句根据某个字段进行统计，如果该字段某一行的值为null的话，将不会被统计。所以可以在count(*)
来实现。*
表示所有字段数据，一行中也不可能所有的数据都为null，所以建议使用
count(*)</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">select</span> <span class="hljs-built_in">count</span>(<span class="hljs-operator">*</span>) <span class="hljs-keyword">from</span> stu;<br></code></pre></td></tr></table></figure></li>
<li><p>查询数学成绩的最高分</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">select</span> <span class="hljs-built_in">max</span>(math) <span class="hljs-keyword">from</span> stu;<br></code></pre></td></tr></table></figure></li>
<li><p>查询数学成绩的最低分</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">select</span> <span class="hljs-built_in">min</span>(math) <span class="hljs-keyword">from</span> stu;<br></code></pre></td></tr></table></figure></li>
<li><p>查询数学成绩的总分</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">select</span> <span class="hljs-built_in">sum</span>(math) <span class="hljs-keyword">from</span> stu;<br></code></pre></td></tr></table></figure></li>
<li><p>查询数学成绩的平均分</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">select</span> <span class="hljs-built_in">avg</span>(math) <span class="hljs-keyword">from</span> stu;<br></code></pre></td></tr></table></figure></li>
<li><p>查询英语成绩的最低分</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">select</span> <span class="hljs-built_in">min</span>(english) <span class="hljs-keyword">from</span> stu;<br></code></pre></td></tr></table></figure></li>
</ul>
<h3 id="分组查询">8.5 分组查询</h3>
<h4 id="语法-3">8.5.1 语法</h4>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> 字段列表 <span class="hljs-keyword">FROM</span> 表名 [<span class="hljs-keyword">WHERE</span> 分组前条件限定] <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> 分组字段名 [<span class="hljs-keyword">HAVING</span> 分组后条件过滤];<br></code></pre></td></tr></table></figure>
<blockquote>
<p>注意：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义</p>
</blockquote>
<h4 id="练习-3">8.5.2 练习</h4>
<ul>
<li><p>查询男同学和女同学各自的数学平均分</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">select</span> sex, <span class="hljs-built_in">avg</span>(math) <span class="hljs-keyword">from</span> stu <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> sex;<br></code></pre></td></tr></table></figure>
<blockquote>
<p>注意：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">select</span> name, sex, <span class="hljs-built_in">avg</span>(math) <span class="hljs-keyword">from</span> stu <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> sex;  <span class="hljs-comment">-- 这里查询name字段就没有任何意义</span><br></code></pre></td></tr></table></figure></li>
<li><p>查询男同学和女同学各自的数学平均分，以及各自人数</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">select</span> sex, <span class="hljs-built_in">avg</span>(math),<span class="hljs-built_in">count</span>(<span class="hljs-operator">*</span>) <span class="hljs-keyword">from</span> stu <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> sex;<br></code></pre></td></tr></table></figure></li>
<li><p>查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70分的不参与分组</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">select</span> sex, <span class="hljs-built_in">avg</span>(math),<span class="hljs-built_in">count</span>(<span class="hljs-operator">*</span>) <span class="hljs-keyword">from</span> stu <span class="hljs-keyword">where</span> math <span class="hljs-operator">&gt;</span> <span class="hljs-number">70</span> <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> sex;<br></code></pre></td></tr></table></figure></li>
<li><p>查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70分的不参与分组，分组之后人数大于2个的</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">select</span> sex, <span class="hljs-built_in">avg</span>(math),<span class="hljs-built_in">count</span>(<span class="hljs-operator">*</span>) <span class="hljs-keyword">from</span> stu <span class="hljs-keyword">where</span> math <span class="hljs-operator">&gt;</span> <span class="hljs-number">70</span> <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> sex <span class="hljs-keyword">having</span> <span class="hljs-built_in">count</span>(<span class="hljs-operator">*</span>)  <span class="hljs-operator">&gt;</span> <span class="hljs-number">2</span>;<br></code></pre></td></tr></table></figure></li>
</ul>
<p><strong>where 和 having 区别：</strong></p>
<ul>
<li><p>执行时机不一样：where
是分组之前进行限定，不满足where条件，则不参与分组，而having是分组之后对结果进行过滤。</p></li>
<li><p>可判断的条件不一样：where 不能对聚合函数进行判断，having
可以。</p></li>
</ul>
<h3 id="分页查询">8.6 分页查询</h3>
<p>如下图所示，大家在很多网站都见过类似的效果，如京东、百度、淘宝等。分页查询是将数据一页一页的展示给用户看，用户也可以通过点击查看下一页的数据。</p>
<img src="/2022/05/05/mysql%E5%9F%BA%E7%A1%80/image-20220505164931399.png" srcset="/img/loading.gif" lazyload class="">
<p>接下来我们先说分页查询的语法。</p>
<h4 id="语法-4">8.6.1 语法</h4>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> 字段列表 <span class="hljs-keyword">FROM</span> 表名 LIMIT  起始索引 , 查询条目数;<br></code></pre></td></tr></table></figure>
<blockquote>
<p>注意： 上述语句中的起始索引是从0开始</p>
</blockquote>
<h4 id="练习-4">8.6.2 练习</h4>
<ul>
<li><p>从0开始查询，查询3条数据</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">select</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> stu limit <span class="hljs-number">0</span> , <span class="hljs-number">3</span>;<br></code></pre></td></tr></table></figure></li>
<li><p>每页显示3条数据，查询第1页数据</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">select</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> stu limit <span class="hljs-number">0</span> , <span class="hljs-number">3</span>;<br></code></pre></td></tr></table></figure></li>
<li><p>每页显示3条数据，查询第2页数据</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">select</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> stu limit <span class="hljs-number">3</span> , <span class="hljs-number">3</span>;<br></code></pre></td></tr></table></figure></li>
<li><p>每页显示3条数据，查询第3页数据</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">select</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> stu limit <span class="hljs-number">6</span> , <span class="hljs-number">3</span>;<br></code></pre></td></tr></table></figure></li>
</ul>
<p>从上面的练习推导出起始索引计算公式：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql">起始索引 <span class="hljs-operator">=</span> (当前页码 <span class="hljs-operator">-</span> <span class="hljs-number">1</span>) <span class="hljs-operator">*</span> 每页显示的条数<br></code></pre></td></tr></table></figure>

            </div>
            <hr>
            <div>
              <div class="post-metas mb-3">
                
                  <div class="post-meta mr-3">
                    <i class="iconfont icon-category"></i>
                    
                      <a class="hover-with-bg" href="/categories/java/">java</a>
                    
                  </div>
                
                
                  <div class="post-meta">
                    <i class="iconfont icon-tags"></i>
                    
                      <a class="hover-with-bg" href="/tags/mysql%E5%9F%BA%E7%A1%80/">mysql基础</a>
                    
                  </div>
                
              </div>
              
                <p class="note note-warning">
                  
                    本博客所有文章除特别声明外，均采用 <a target="_blank" href="https://creativecommons.org/licenses/by-sa/4.0/deed.zh" rel="nofollow noopener noopener">CC BY-SA 4.0 协议</a> ，转载请注明出处！
                  
                </p>
              
              
                <div class="post-prevnext">
                  <article class="post-prev col-6">
                    
                    
                      <a href="/2022/05/05/hello-world/">
                        <i class="iconfont icon-arrowleft"></i>
                        <span class="hidden-mobile">Hello World</span>
                        <span class="visible-mobile">上一篇</span>
                      </a>
                    
                  </article>
                  <article class="post-next col-6">
                    
                    
                      <a href="/2022/05/05/JAVA%E5%9F%BA%E7%A1%80/">
                        <span class="hidden-mobile">java基础</span>
                        <span class="visible-mobile">下一篇</span>
                        <i class="iconfont icon-arrowright"></i>
                      </a>
                    
                  </article>
                </div>
              
            </div>

            
          </article>
        </div>
      </div>
    </div>
    
      <div class="d-none d-lg-block col-lg-2 toc-container" id="toc-ctn">
        <div id="toc">
  <p class="toc-header"><i class="iconfont icon-list"></i>&nbsp;目录</p>
  <div class="toc-body" id="toc-body"></div>
</div>

      </div>
    
  </div>
</div>

<!-- Custom -->


    

    
      <a id="scroll-top-button" aria-label="TOP" href="#" role="button">
        <i class="iconfont icon-arrowup" aria-hidden="true"></i>
      </a>
    

    
      <div class="modal fade" id="modalSearch" tabindex="-1" role="dialog" aria-labelledby="ModalLabel"
     aria-hidden="true">
  <div class="modal-dialog modal-dialog-scrollable modal-lg" role="document">
    <div class="modal-content">
      <div class="modal-header text-center">
        <h4 class="modal-title w-100 font-weight-bold">搜索</h4>
        <button type="button" id="local-search-close" class="close" data-dismiss="modal" aria-label="Close">
          <span aria-hidden="true">&times;</span>
        </button>
      </div>
      <div class="modal-body mx-3">
        <div class="md-form mb-5">
          <input type="text" id="local-search-input" class="form-control validate">
          <label data-error="x" data-success="v"
                 for="local-search-input">关键词</label>
        </div>
        <div class="list-group" id="local-search-result"></div>
      </div>
    </div>
  </div>
</div>
    

    
  </main>

  <footer class="text-center mt-5 py-3">
  <div class="footer-content">
     <a href="https://hexo.io" target="_blank" rel="nofollow noopener"><span>Hexo</span></a> <i class="iconfont icon-love"></i> <a href="https://github.com/fluid-dev/hexo-theme-fluid" target="_blank" rel="nofollow noopener"><span>Fluid</span></a> 
  </div>
  

  

  
</footer>


  <!-- SCRIPTS -->
  
  <script  src="https://cdn.jsdelivr.net/npm/nprogress@0/nprogress.min.js" ></script>
  <link  rel="stylesheet" href="https://cdn.jsdelivr.net/npm/nprogress@0/nprogress.min.css" />

  <script>
    NProgress.configure({"showSpinner":false,"trickleSpeed":100})
    NProgress.start()
    window.addEventListener('load', function() {
      NProgress.done();
    })
  </script>


<script  src="https://cdn.jsdelivr.net/npm/jquery@3/dist/jquery.min.js" ></script>
<script  src="https://cdn.jsdelivr.net/npm/bootstrap@4/dist/js/bootstrap.min.js" ></script>
<script  src="/js/events.js" ></script>
<script  src="/js/plugins.js" ></script>

<!-- Plugins -->


  <script  src="/js/local-search.js" ></script>



  
    <script  src="/js/img-lazyload.js" ></script>
  



  



  
    <script  src="https://cdn.jsdelivr.net/npm/tocbot@4/dist/tocbot.min.js" ></script>
  
  
    <script  src="https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@3/dist/jquery.fancybox.min.js" ></script>
  
  
    <script  src="https://cdn.jsdelivr.net/npm/anchor-js@4/anchor.min.js" ></script>
  
  
    <script defer src="https://cdn.jsdelivr.net/npm/clipboard@2/dist/clipboard.min.js" ></script>
  






  <script  src="https://cdn.jsdelivr.net/npm/typed.js@2/lib/typed.min.js" ></script>
  <script>
    (function (window, document) {
      var typing = Fluid.plugins.typing;
      var title = document.getElementById('subtitle').title;
      
        typing(title);
      
    })(window, document);
  </script>















<!-- 主题的启动项 保持在最底部 -->
<script  src="/js/boot.js" ></script>


<script src="/live2dw/lib/L2Dwidget.min.js?094cbace49a39548bed64abff5988b05"></script><script>L2Dwidget.init({"pluginRootPath":"live2dw/","pluginJsPath":"lib/","pluginModelPath":"assets/","tagMode":false,"debug":false,"model":{"jsonPath":"/live2dw/assets/assets/Epsilon2.1.model.json"},"display":{"position":"top","width":300,"height":600,"hOffset":-10,"vOffset":0},"mobile":{"show":true},"react":{"opacity":0.7},"post":{"math":{"enable":true,"specific":true,"engine":"mathjax"}},"log":false});</script></body>
</html>
